package cn.com.callback

import cn.com.kh.MSUtil
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import cn.com.elite.EliteBean;
import cn.com.elite.PropertiesUtil;
import cn.com.elite.Util;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONObject;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

/**
 *
 * @author msjykf
 * 将每晚待执行邮件批次依次取出来推送到安正数据中心执行，数据返回CallCenter后更新批次表状态
 * {"batch_id":"","template_id":"","requesttype": 1}
 * batch_id：批次号；template_id：邮件模板ID；requesttype：请求类型 1定时任务调度 2动态页面调度
 *
 */
public class EmailBatchRequest implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(EmailBatchRequest.class);

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {
        try {
            def jsonbody = Util.getRequestBody(request, 'utf-8'); //获取报文
            //log.debug("jsonbody:"+jsonbody);
            if (jsonbody.equals("")) {
                return "{\"msg\": \"未获取到入参报文！\",\"code\": -2}"
            };
            JSONObject obj = JSONObject.fromObject(jsonbody);
            def template_id = Util.filterForObject(obj.get("template_id"));
            if (template_id.equals("")) {
                return "{\"msg\": \"template_id参数不能为空！\",\"code\": -2}"
            };
            def requesttype = Util.filterForObject(obj.get("requesttype"));
            if (requesttype.equals("")) {
                return "{\"msg\": \"requesttype参数不能为空！\",\"code\": -2}"
            };
            PropertiesUtil properties = new PropertiesUtil();
            def ccserverip = properties.readValue("ccserverip"); //获取CallCenter访问地址
            def serverpath = properties.readValue("serverpath"); //获取EliteGeneralHttpTask服务路径
            def birthdaysmsid = properties.readValue("birthdaysmsid"); //生日祝福短信模板ID
            def batch_id = "";    //批次
            def tbname = "";    //客户分组表名
            def filepath = "";    //邮件模板存放相对路径
            def filename = ""; //邮件模板存储文件名称
            def c_taskcode = ""; //邮件模板固定taskcode
            def saletype = ""; //销售类型
            def checkstate = 0; //审核状态
            def datafrom = ""; //数据来源：0安正接口 1客户分组
            def subject = ""; //邮件模板名称
            def pipe = "" //发送通道
            def priority = "" //优先级
            def params = ""; //控制参数
            def email_distinct = 0;//是否需要去重
            def emailFileName = "";
            def PI_TPLID = ""; //old邮件模板ID
            def i = 0;
            def smap = eliteBean.chaxun_sql("select dt.description,dt.documentpath,dt.filename,dt.datafrom,dt.c_taskcode,dt.saletype,nvl(dp.docaudit,0) as checkstate,dp.priority,dp.channel,ma.customergroup_tablename,pf.totalid,dt.email_distinct from doctemplate dt left join docproperty dp on dt.doctemplate_id=dp.doctemplate_id left join multimediaaddressee ma on dt.doctemplate_id=ma.doctemplate_id left join preferences pf on dt.doctemplate_id=pf.doctemplate_id where dt.doctype=2 and dt.doctemplate_id='" + template_id + "'", "dataSource");
            if (smap.size() == 0) {
                return "{\"msg\": \"无效的邮件模板！\",\"code\": -2}"
            } else {
                filepath = Util.filterForObject(smap[0].get("documentpath"));
                emailFileName = Util.filterForObject(smap[0].get("filename"));

                filename = template_id + Util.filterForObject(smap[0].get("filename"));
                checkstate = Util.filterForObject(smap[0].get("checkstate"));
                tbname = Util.filterForObject(smap[0].get("customergroup_tablename"));
                datafrom = Util.filterForObject(smap[0].get("datafrom"));
                c_taskcode = Util.filterForObject(smap[0].get("c_taskcode"));
                saletype = Util.filterForObject(smap[0].get("saletype"));
                params = Util.filterForObject(smap[0].get("totalid"));
                subject = Util.filterForObject(smap[0].get("description"));
                pipe = Util.filterForObject(smap[0].get("channel"));
                priority = Util.filterForObject(smap[0].get("priority"));
                email_distinct = Util.filterForObject(smap[0].get("email_distinct"));
            };
            if (datafrom.equals("")) {
                return "{\"msg\": \"数据来源不能为空！\",\"code\": -2}"
            } else if (datafrom.equals("1") && tbname.equals("")) {
                return "{\"msg\": \"客户分组不能为空！\",\"code\": -2}"
            } else if (datafrom.equals("1")) {
                StringBuffer sb = new StringBuffer();
                String[] temp = tbname.split(",");
                for (i = 0; i < temp.length; i++) {
                    switch (i) {
                        case 0:
                            sb.append("'" + temp[i] + "'");
                            break;
                        default:
                            sb.append(",'" + temp[i] + "'");
                            break;
                    }
                };
                smap = eliteBean.chaxun_sql("select min(nvl(gs.acceptstate,1)) as state from customer_group_list gl left join customer_group_synchis gs on gl.lastrunbatch_id=gs.batch_id where gl.customergroup_tablename in (" + sb + ")", "dataSource");
                if (Util.filterForObject(smap[0].get("state")).equals("0")) {
                    return "{\"msg\": \"客户分组数据未执行完！\",\"code\": -2}"
                };
            };
            if (requesttype.equals("1")) {
                batch_id = Util.filterForObject(obj.get("batch_id"));
                if (batch_id.equals("")) {
                    return "{\"msg\": \"批次号不能为空！\",\"code\": -2}"
                }
            } else if (requesttype.equals("2")) {
                smap = eliteBean.chaxun_sql("select customgetid('EM') as batch_id from dual", "dataSource");
                batch_id = smap[0].get("batch_id");

                //手工调度的时候将当前模板filename插入到email_send_batch表中
                eliteBean.update("insert into email_send_batch(batch_id,template_id,checkstate,expectedstartdate,template_name,emailfailename) values('" + batch_id + "','" + template_id + "'," + checkstate + ",sysdate,'" + subject + "','" + emailFileName + "')", "dataSource");
                if (c_taskcode.equals("0001")) {
                    eliteBean.update("insert into sms_send_batch(batch_id,sms_id,checkstate,createdate,expectedstartdate,state) values(customgetid('MS'),'" + birthdaysmsid + "',1,sysdate,sysdate,0)", "dataSource");
                };
            };
            def httpurl = "http://" + ccserverip + "/ngs/fs/get?file=" + filepath + "&d=true"; //邮件模板下载地址
            def savefile = serverpath + filename;
            if (filepath != '') {
                boolean flag = Util.httpDownLoadFile(httpurl, savefile, 10000); //下载邮件模板
            };

            def smap1 = eliteBean.chaxun_sql("SELECT PI_TPLID FROM  preferences WHERE DOCTEMPLATE_ID='" + template_id + "'", "dataSource");
            if (smap1.size() > 0) {
                PI_TPLID = Util.filterForObject(smap1[0].get("PI_TPLID"));
            }
            if (datafrom.equals("0") || (datafrom.equals("1") && PI_TPLID != '' && PI_TPLID != null)) {
                log.debug("=========安正数据接口取数据模式===========");
                def ibqurl = properties.readValue("ibqurl"); //获取webservice接口地址
                def appid = properties.readValue("appid"); //获取appId
                def sqlresult = "";
                def charset = "utf-8"; //字符集
                def timeout = 100000; //请求超时时间
                def xmlbody = "";
                if (params.equals("")) {
                    params = """<params>
							<isOutDirectionParam>false</isOutDirectionParam>
							<name>PI_DK_SYSTEM</name>
							<value>*</value>
					</params>
 					<params>
							<isOutDirectionParam>false</isOutDirectionParam>
							<name>PI_AGENCYNO</name>
							<value>*</value>
					</params>
					 <params>
							<isOutDirectionParam>false</isOutDirectionParam>
							<name>PI_SK_AGENCY</name>
							<value>*</value>
					</params>
 					<params>
							<isOutDirectionParam>false</isOutDirectionParam>
							<name>PI_BK_PRODUCT</name>
							<value>*</value>
					</params>
 					<params>
							<isOutDirectionParam>false</isOutDirectionParam>
							<name>PI_SK_DATE</name>
							<value>*</value>
					</params>
 					<params>
							<isOutDirectionParam>false</isOutDirectionParam>
							<name>PI_CUSTID</name>
							<value>*</value>
					</params>""";
                };
                switch (c_taskcode) {
                    case '0001':
                        xmlbody = """<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
							<SOAP-ENV:Body>
								<m:executeProcedure xmlns:m="http://service.web.server.ua.businessmatrix.com/">
									<appId>""" + appid + """</appId>
										<name>s-P_EMAIL_BIRTH</name>
										<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_EMAIL_ID</name>
											<value>""" + template_id + """</value>
									</params>
									<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_BATCHNO</name>
										<value>""" + batch_id + """</value>
										</params>
									</m:executeProcedure>
								</SOAP-ENV:Body>
							</SOAP-ENV:Envelope>""";
                        break;
                    case '0002':
                        xmlbody = """<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
								<SOAP-ENV:Body>
									<m:executeProcedure xmlns:m="http://service.web.server.ua.businessmatrix.com/">
										<appId>""" + appid + """</appId>
									<name>s-P_EMAIL_DUE</name>
									<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_EMAIL_ID</name>
											<value>""" + template_id + """</value>
										</params>
										<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_BATCHNO</name>
											<value>""" + batch_id + """</value>
									</params>
									""" + params + """
										</m:executeProcedure>
									</SOAP-ENV:Body>
								</SOAP-ENV:Envelope>""";
                        break;
                    case '0003':
                        savefile = serverpath + "FinancialDueMould.html";
                        xmlbody = """<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
								<SOAP-ENV:Body>
									<m:executeProcedure xmlns:m="http://service.web.server.ua.businessmatrix.com/">
										<appId>""" + appid + """</appId>
									<name>s-P_EMAIL_DIV</name>
									<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_EMAIL_ID</name>
										<value>""" + template_id + """</value>
										</params>
										<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_BATCHNO</name>
											<value>""" + batch_id + """</value>
									</params>
									""" + params + """
										</m:executeProcedure>
									</SOAP-ENV:Body>
								</SOAP-ENV:Envelope>""";
                        break;
                    case '0004':
                        //基金交易确认
                        xmlbody = """<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
								<SOAP-ENV:Body>
									<m:executeProcedure xmlns:m="http://service.web.server.ua.businessmatrix.com/">
										<appId>""" + appid + """</appId>
									<name>s-P_EMAIL_TRAN</name>
									<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_EMAIL_ID</name>
										<value>""" + template_id + """</value>
										</params>
										<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_BATCHNO</name>
											<value>""" + batch_id + """</value>
									</params>
									""" + params + """
										</m:executeProcedure>
									</SOAP-ENV:Body>
								</SOAP-ENV:Envelope>""";
                        break;
                    case '0005':
                        xmlbody = """<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
								<SOAP-ENV:Body>
									<m:executeProcedure xmlns:m="http://service.web.server.ua.businessmatrix.com/">
										<appId>""" + appid + """</appId>
									<name>s-P_EMAIL_ACCOTRAN</name>
									<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_EMAIL_ID</name>
										<value>""" + template_id + """</value>
										</params>
										<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_BATCHNO</name>
											<value>""" + batch_id + """</value>
									</params>
									""" + params + """
										</m:executeProcedure>
									</SOAP-ENV:Body>
								</SOAP-ENV:Envelope>""";
                        break;
                    case '0006':
                        if (datafrom.equals("0") && (PI_TPLID.equals("") || PI_TPLID == null)) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                            Calendar cal1 = Calendar.getInstance();
                            cal1.add(Calendar.MONTH, -1);
                            cal1.set(Calendar.DAY_OF_MONTH, 1);
                            def firstDay;
                            def lastDay;
                            //def firstDay = sdf.format(cal1.getTime()); //取上月第一天
                            Calendar cal2 = Calendar.getInstance();
                            cal2.set(Calendar.DAY_OF_MONTH, 0);
                            //def lastDay = sdf.format(cal2.getTime()); //取上月最后一天

                            //20190312邮件模板内容时间
                            def sk_kdate = eliteBean.chaxun_sql("select to_char(sk_date,'yyyymmdd') as sk_date,to_char(sk_edate,'yyyymmdd')as sk_edate from preferences where doctemplate_id='" + template_id + "'", "dataSource");

                            String sk_date = sk_kdate.get(0).get("sk_date");
                            String sk_edate = sk_kdate.get(0).get("sk_edate");
                            //log.debug("月度对账单配置的开始数据时间:"+sk_date);
                            //log.debug("月度对账单配置的结束数据时间:"+sk_edate);

                            if (sk_date.equals("") || sk_edate.equals("") || sk_date == null || sk_edate == null) {
                                firstDay = sdf.format(cal1.getTime()); //取上月第一天
                                lastDay = sdf.format(cal2.getTime()); //取上月最后一天
                            } else {
                                firstDay = sk_date;
                                lastDay = sk_edate; //取数模板配置时间
                            };

                            xmlbody = """<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
									<SOAP-ENV:Body>
										<m:executeProcedure xmlns:m="http://service.web.server.ua.businessmatrix.com/">
											<appId>""" + appid + """</appId>
										<name>s-P_EMAIL_BILL</name>
										<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_EMAIL_ID</name>
											<value>""" + template_id + """</value>
											</params>
											<params>
												<isOutDirectionParam>false</isOutDirectionParam>
												<name>PI_STARTDATE</name>
												<value>""" + firstDay + """</value>
										</params>
										<params>
											<isOutDirectionParam>false</isOutDirectionParam>
											<name>PI_ENDDATE</name>
											<value>""" + lastDay + """</value>
											</params>
											<params>
												<isOutDirectionParam>false</isOutDirectionParam>
												<name>PI_BATCHNO</name>
												<value>""" + batch_id + """</value>
										</params>
										""" + params + """
											</m:executeProcedure>
										</SOAP-ENV:Body>
									</SOAP-ENV:Envelope>""";
                        } else {
                            def firstDay;
                            def lastDay;
                            def defdate;
                            //20200716获取机构对账单邮件模板内容时间
                            def sk_kdate = eliteBean.chaxun_sql("select to_char(sk_date,'yyyymmdd') as sk_date,to_char(sk_edate,'yyyymmdd')as sk_edate from triggermode where doctemplate_id='" + template_id + "'", "dataSource");

                            String sk_date = sk_kdate.get(0).get("sk_date");
                            String sk_edate = sk_kdate.get(0).get("sk_edate");
                            //log.debug("机构对账单配置的开始数据时间:"+sk_date);
                            //log.debug("机构对账单配置的结束数据时间:"+sk_edate);

                            //获取前一天时间
                            //defdate = eliteBean.chaxun_sql("SELECT to_char(sysdate-1,'yyyymmdd') AS defstartDay from dual","dataSource");
                            //firstDay=defdate.get(0).get("defstartDay");
                            //lastDay=defdate.get(0).get("defstartDay");

                            defdate = eliteBean.chaxun_sql("select max(DATE_I) traday from c_calendar where workflag=1 and DATE_I<=to_char(sysdate,'yyyymmdd')-1", "dataSource");
                            if (sk_date.equals("") || sk_date == null) {//获取最后一个交易日
                                firstDay = defdate.get(0).get("traday");
                                lastDay = defdate.get(0).get("traday");
                            } else {
                                firstDay = sk_date;
                                if (sk_edate.equals("") || sk_edate == null) {
                                    lastDay = defdate.get(0).get("traday");
                                } else {
                                    lastDay = sk_edate;
                                }
                            };
                            //log.debug("机构对账单配置的最终开始时间:"+firstDay);
                            //log.debug("机构对账单配置的最终结束时间:"+lastDay);

                            String[] table = tbname.split(",");
                            def defkhbh;
                            def messages = '';
                            for (i = 0; i < table.length; i++) {
                                try {
                                    defkhbh = eliteBean.chaxun_sql(" select TO_CHAR(WMSYS.WM_CONCAT(SK_INVPTY)) as yhbh from " + table[i] + "", "dataSource");
                                    messages = messages + ',' + defkhbh.get(0).get("yhbh");
                                } catch (Exception e) {
                                    log.error("ErrorMessage:", e);
                                };
                            };
                            //log.debug("机构对账单分组客户编号:"+messages);
                            messages = messages.substring(1) + ":IN";
                            //log.debug("机构对账单分组处理后的客户编号:"+messages);

                            //手动拼接机构客户的用户编号
                            params = """<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_DK_SYSTEM</name>
										<value>*</value>
								</params>
								<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_AGENCYNO</name>
										<value>*</value>
								</params>
								 <params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_SK_AGENCY</name>
										<value>*</value>
								</params>
								<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_BK_PRODUCT</name>
										<value>*</value>
								</params>
								<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_SK_DATE</name>
										<value>*</value>
								</params>
								<params>
										<isOutDirectionParam>false</isOutDirectionParam>
										<name>PI_CUSTID</name>
										<value>""" + messages + """</value>
								</params>""";

                            xmlbody = """<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
										<SOAP-ENV:Body>
											<m:executeProcedure xmlns:m="http://service.web.server.ua.businessmatrix.com/">
												<appId>""" + appid + """</appId>
											<name>s-P_EMAIL_BILL</name>
											<params>
												<isOutDirectionParam>false</isOutDirectionParam>
												<name>PI_EMAIL_ID</name>
												<value>""" + PI_TPLID + """</value>
												</params>
												<params>
													<isOutDirectionParam>false</isOutDirectionParam>
													<name>PI_STARTDATE</name>
													<value>""" + firstDay + """</value>
											</params>
											<params>
												<isOutDirectionParam>false</isOutDirectionParam>
												<name>PI_ENDDATE</name>
												<value>""" + lastDay + """</value>
												</params>
												<params>
													<isOutDirectionParam>false</isOutDirectionParam>
													<name>PI_BATCHNO</name>
													<value>""" + batch_id + """</value>
											</params>
											""" + params + """
												</m:executeProcedure>
											</SOAP-ENV:Body>
										</SOAP-ENV:Envelope>""";
                        }
                        break;
                };

                log.debug("当前调用接口的报文" + xmlbody);

                if (!xmlbody.equals("")) {
                    def result = Util.callWebService(ibqurl, xmlbody, charset, "", timeout);
                    String[] data = new String[2];
                    data[0] = "update email_send_batch set state=0,inputmsg='" + xmlbody + "',outputmsg='" + result + "',requesttime=sysdate where batch_id='" + batch_id + "'";
                    data[1] = "update docproperty set no='" + batch_id + "' where doctemplate_id='" + template_id + "'";
                    int[] dataresult = eliteBean.updateBatch(data, "dataSource");
                    return "{\"msg\": \"请求接口成功！\",\"code\": 1}"
                } else {
                    return "{\"msg\": \"未找到对应的模板！\",\"code\": -2}"
                }
            } else if (datafrom.equals("1")) {
                log.debug("=========客户分组取数据模式===========");
                //客户分组取数据
                JSONObject jsonout = new JSONObject();
                def content = MSUtil.getMergeContent(JSONObject.fromObject(jsonout).toString(), "/data/template", "" + filename + "", "gbk");
                //log.debug("content:"+content);
                MapSqlParameterSource sqlparams = new MapSqlParameterSource(); //sql防注入方式
                sqlparams.addValue("batch_id", batch_id);
                sqlparams.addValue("template_id", template_id);
                sqlparams.addValue("priority", priority);
                sqlparams.addValue("pipe", pipe);
                sqlparams.addValue("subject", subject);
                sqlparams.addValue("content", content);
                String[] table = tbname.split(",");
                def sqls = 0;
                def messages = 0;
                def exclusions = ""; //邮件黑名单过滤条件
                if (saletype) {
                    exclusions = "select sk_invpty from specialphone where blacktype='EMAIL' and status=1 and sk_invpty is not null and saletype in(0," + saletype + ")";
                } else {
                    exclusions = "select sk_invpty from specialphone where blacktype='EMAIL' and status=1 and sk_invpty is not null and saletype=0";

                };
                def regex_email = "^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+((\\.[a-zA-Z0-9_-]{2,3}){1,2})\$"; //邮箱正则表达式
                for (i = 0; i < table.length; i++) {
                    try {
                        sqls = eliteBean.update_sql_pro("insert into email_send_task(rec_guid,batch_id,template_id,sk_invpty,invpty_name,email,contenttype,state,priority,pipe,createdate,expectedstartdate,subject) select sysguid,:batch_id,:template_id,sk_invpty,invpty_name,email,'html',0,:priority,:pipe,sysdate,sysdate,:subject from " + table[i] + " where regexp_like(email,'" + regex_email + "') and sk_invpty not in (" + exclusions + ")", sqlparams, "dataSource");
                        messages = messages + sqls;
                        //log.debug("messages:"+messages);
                    } catch (Exception e) {
                        log.error("ErrorMessage:", e);
                    };
                };
                //邮箱去重处理
                log.debug("=========邮箱去重处理2===========");
                log.debug(email_distinct);
                if (email_distinct.equals("1")) {
                    //eliteBean.update("delete from email_send_task where lower(email) in (select lower(email) from email_send_task where batch_id='"+batch_id+"' group by lower(email) having count(lower(email))>1)and rowid not in (select min(rowid) from email_send_task where batch_id='"+batch_id+"' group by lower(email) having count(*)>1) and batch_id='"+batch_id+"'","dataSource");

                    smap = eliteBean.chaxun_sql("select Lower(email) As email,max(sk_invpty) as sk_invpty,count(*) as findrow from email_send_task where batch_id='" + batch_id + "' group by Lower(email) Having count(*)>1", "dataSource");
                    for (i = 0; i < smap.size(); i++) {
                        eliteBean.update("delete from email_send_task where batch_id='" + batch_id + "' and lower(email)='" + smap[i].get("email") + "' and sk_invpty<>" + smap[i].get("sk_invpty") + "", "dataSource");
                        messages = messages - smap[i].get("findrow");
                        messages++;
                    };
                    //smap = eliteBean.chaxun_sql("select count(*) as messages from email_send_task where batch_id='"+batch_id+"' ","dataSource");
                    //messages = smap[0].get("messages");
                    //log.debug("=========邮箱去重处理33===========");
                };

                eliteBean.update_sql_pro("update email_send_task set content=:content where batch_id=:batch_id", sqlparams, "dataSource");
                String[] data = new String[2];
                data[0] = "update email_send_batch set messages=" + messages + ",quantityvalid=quantityvalid+" + messages + ",state=3,requesttime=sysdate,acceptdate=sysdate,comments='数据推送成功' where batch_id='" + batch_id + "'"
                data[1] = "update docproperty set no='" + batch_id + "' where doctemplate_id='" + template_id + "'";
                int[] dataresult = eliteBean.updateBatch(data, "dataSource");
                return "{\"msg\": \"请求接口成功！\",\"code\": 1}"
            }
        } catch (Exception e) {
            log.error("ErrorMessage:", e);
            return "{\"msg\": \"请求接口失败！\",\"code\": -1}"
        }
    }
    //取传入时间的第一天
    public static String getFirstDay(String nowDate) {
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
        Calendar calendar = Calendar.getInstance();

        Date theDate = null;
        if (nowDate == null || nowDate.length() <= 0) {
            theDate = calendar.getTime();
        } else {
            try {
                theDate = df.parse(nowDate);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }

        GregorianCalendar gcLast = (GregorianCalendar) Calendar.getInstance();
        gcLast.setTime(theDate);
        gcLast.set(Calendar.DAY_OF_MONTH, 1);
        String day_first = df.format(gcLast.getTime());
        StringBuffer str = new StringBuffer().append(day_first);
        return str.toString();

    }
    //取传入时间的最后一天
    public static String getLastDay(String nowDate) {
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
        Calendar calendar = Calendar.getInstance();
        Date theDate = null;

        if (nowDate == null || nowDate.length() <= 0) {
            theDate = calendar.getTime();
        } else {
            try {
                theDate = df.parse(nowDate);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }

        GregorianCalendar gcLast = (GregorianCalendar) Calendar.getInstance();
        gcLast.setTime(theDate);
        gcLast.add(Calendar.MONTH, 1);
        gcLast.set(Calendar.DATE, 1);
        gcLast.add(Calendar.DATE, -1);
        String day_last = df.format(gcLast.getTime());
        StringBuffer endStr = new StringBuffer().append(day_last);
        day_last = endStr.toString();
        StringBuffer str = new StringBuffer().append(day_last);
        return str.toString();
    }
}